// PURPOSE: Import a subset of variables from datasets in the data_final folder
// and save as dta files.

clear all

// SET MACROS
global Input  "O:/data_final"
global Output "Y:/limited/Michigan_CTE/funding_change/data_raw"

// IMPORT DATA

/*
import excel "Y:/limited/Michigan_CTE/funding_change/data_raw/X0107-2006-2009.xlsx", clear firstrow
gen year = real(substr(X0107YEAR,-5,.))
ren (CIPCODE REIMAMT Funded) (cipcode_temp amount_temp section)
gen obno_temp = real(obno)
gen psn_temp = real(psn)
keep year *temp section
*/

* 2010-2015
import excel "Y:/limited/Michigan_CTE/funding_change/data_raw/X0107-2010-2015.xlsx", clear firstrow sheet("X017(OLD)")
*NOTE  REIMAMT = REIMRATE * reimbursable_students * mpw /600 * now/18

gen year = real(subinstr(substr(X0107YEAR,-5,.),"-","",.))
tab year
gen funds_total = real(REIMAMT)
gen funds_state60 = real(stateFunds)
gen funds_cepd40 = real(cepdFunds)

encode fullyFunded, gen(full_category)
encode splitField, gen(split_category)

gisid classpk year
gcollapse (sum) funds*  (max) full_category split_category  , by(psn year cepd fano oano obno CIPCODE)
gisid psn year

drop if inlist(CIPCODE,"19.0000","AC.0000","BT.0000","EM.0000","HE.0000","HU.0000","NR.0000")
tempfile temp
save `temp'



* 2016-2021
import excel "Y:/limited/Michigan_CTE/funding_change/data_raw/X0107-2016-2021.xlsx", clear firstrow sheet("X0107(Current)") 

gen year = real(substr(RepYear,-4,.))
tab year
gen funds_state60 = real(StateFunds60)
gen funds_cepd40 = real(PSNCEPDFunds40)
gen funds_total = funds_state60 + funds_cepd40

keep funds* PSN year CEPDNo FANo OANo OBNo CIPCode
gduplicates drop
gisid PSN year

ren (PSN year CEPDNo FANo OANo OBNo CIPCode) (psn year cepd fano oano obno CIPCODE)
append using `temp'

**
*
**

destring cepd, gen(c)
destring fano, gen(f)
destring oano, gen(a)
destring obno, gen(b)
destring psn, gen(p)

drop cepd fano oano obno psn

ren  (c f a b p) (cepd fano oano obno psn)


save "Y:/limited/Michigan_CTE/funding_change/data_final/psn_funding.dta", replace


*Cost Factor List
import excel "Y:/limited/Michigan_CTE/funding_change/data_raw/X0107-2016-2021.xlsx", clear firstrow sheet("X0107(Current)") 

gen year= real(substr(RepYear,-4,.))
keep if year<2020

keep CIPCode CIPName CostFactor year
gduplicates drop

bys CIPC: replace CIPN = CIPN[1]
bys CIPC: gegen costfactor  = median(real(CostFactor))
replace costfactor = 10 if costfactor == 7.5
drop year CostFactor
duplicates drop

ren (CIPCode CIPName) (cipcode progmane)
sort cipcode
save "Y:/limited/Michigan_CTE/funding_change/data_raw/cte_2015_costlist.dta", replace
	 
	 